package com.sq.partner.manager.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class EncryptAndUpdateTBRUtil {
	public static List<Map<String, Object>> getPwd(Connection conn) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Map<String, Object>> list = null;
		try {
			String sql = "select user_code,user_pwd from t_facade_user where user_pwd like '%PWD_NOT_CHG%'";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			list = new ArrayList<Map<String, Object>>();
			ResultSetMetaData md = rs.getMetaData();
			int columnCount = md.getColumnCount();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int i = 1; i <= columnCount; i++) {
					map.put(md.getColumnName(i), rs.getObject(i));
				}

				list.add(map);
			}
		}finally {
			rs.close();
			pstmt.close();
		}
		return list;
	}

	public static int update(PreparedStatement pstmt,String pwd, String epwd, String user) throws SQLException {
		int i = 0;
			
			pstmt.setString(1, epwd);
			pstmt.setString(2, pwd);
			pstmt.setString(3, user);
			i = pstmt.executeUpdate();
		return i;
	}

	public static List<String> selectTBR(Connection conn) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<String> list = null;
		try {
			String sql = "select distinct(channel_code) as no from t_gateway_channel_info where "
					+ "client_no is null and channel_code in (select partner_no from t_trad_insu_plan where "
					+ "insu_plan_no like 'AA%')";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			list = new ArrayList<String>();
			while (rs.next()) {
				String no = rs.getString("no");
				list.add(no);
			}
		} finally {
			JDBCUtil.close(null, pstmt, rs);
		}
		return list;
	}

	public static void updateChannelTBR(Connection conn) throws SQLException {
		PreparedStatement pstmt = null;
		PreparedStatement pstmt2 = null;
		System.out.println();
		System.out.println("----------开始更新投保人编码------------");
		List<String> list = selectTBR(conn);
		try {
			String updateSQL = "update t_gateway_channel_info set client_no = "
					+ "(select applicant_no from t_trad_insu_plan  where  partner_no =? and rownum=1) "
					+ "where channel_code =? ";
			pstmt = conn.prepareStatement(updateSQL);
			String updateTBR = "update t_trad_insu_plan t set applicant_no="
					+ "(select client_no from t_gateway_channel_info a where  partner_no =? "
					+ "and a.channel_code=t.partner_no) "
					+ "where partner_no=?";
			pstmt2 = conn.prepareStatement(updateTBR);
			for (int i = 0; i < list.size(); i++) {
				String partnerno = list.get(i);
				pstmt.setString(1, partnerno);
				pstmt.setString(2, partnerno);
				int m=pstmt.executeUpdate();
			    System.out.println("更新合作伙伴当前"+partnerno+"机构，修改了"+m+"条数据");
			    pstmt2.setString(1, partnerno);
			    pstmt2.setString(2, partnerno);
				int j=pstmt2.executeUpdate();
			    System.out.println("更新投保方案当前"+partnerno+"机构，修改了"+j+"条数据");
			}
		}  finally {
			 pstmt2.close();
			JDBCUtil.close(null, pstmt, null);
		}
		
		System.out.println("----------更新投保人编码完成------------");
	}

	public static void main(String[] args) throws SQLException {
		Connection conn = null;
		PreparedStatement pp = null;
		try {
			conn = JDBCUtil.JDBC();
			conn.setAutoCommit(false);
			List<Map<String, Object>> list = getPwd(conn);
			String pwd = null;
			int cnt = list.size();
			System.out.println("------开始修改密码--------");
			
			String sql = "update t_facade_user set user_pwd=? where user_pwd=? and user_code=?";
			pp = conn.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				Map<String, Object> map = list.get(i);
				pwd = (String) map.get("USER_PWD");
				String usercode = (String) map.get("USER_CODE");
				String ePwd = pwd.replace("PWD_NOT_CHG", "");
				String pwdChg = MD5Util.md5(ePwd);
				update(pp,pwd, pwdChg, usercode);
				System.out.println(usercode + "    " + pwd + "         " + pwdChg);
			}
			
			System.out.println("------修改密码完成------修改记录条数为"+cnt+"--");
			updateChannelTBR(conn);
			System.out.println("---事务已提交-----");
		}catch(Exception e){
			conn.rollback();
			System.out.println("---事务已回滚-----");
			e.printStackTrace();
		}finally{
			pp.close();
			conn.commit();
			conn.close();
		
		}
	}

}
